import pymongo
import datetime
import pymssql

table = ['BillingDetail', 'AccountBilling']
conn = pymssql.connect('172.16.10.30', 'sa', '1qaz@WSX', 'BMS')
cursor = conn.cursor()
field_name = {}
for i in table:
    sql = "select name from syscolumns where id=object_id('{}')".format(i)
    cursor.execute(sql)
    list3 = []
    list2 = cursor.fetchall()
    for j in list2:
        list3.append(j[0])
    conn.commit()
    field_name[i] = tuple(list3)
#print(field_name)

myclient = pymongo.MongoClient(
    "mongodb://root:123456@192.168.200.109:27017/?replicaSet=rs0&authSource=admin")  #
mydb = myclient["bms_dev"]

##构建N条数据的{表：{[字段名:字段]}
datas = {}
for i in table:
    mycol = mydb[i]
    now = datetime.datetime.now()
    yesday = now + datetime.timedelta(days=-1)
    query = {'CreationTime': {'$lte': yesday}}  # 查询条件#yte大于等于，qe等于
    all_list = mycol.find(query).limit(10)
    if i not in datas.keys():##把表作为主键加入到字典中
        datas[i] = []
    for item in all_list:##对mongodb的每条数据进行循环
        data = {}
        for k,v in item.items():#对每条数据的健值对进行循环
            if (type(v).__name__ == 'dict'):#如果数据为字典格式，需要处理后再存储
                for k1,v1 in v.items():#对字典格式的数据进行处理
                    ks = k + '_' + k1#处理健名
                    if (type(v1).__name__ != 'dict') :###如果字典数据里还有字典数据直接舍弃
                        data[ks] = v1
            if (type(v).__name__ != 'dict') and (type(v).__name__ != 'list'):###取正常的健值对数据
                data[k] = v
        print(data)
        datas[i].append(data)#把每条数据加入到最终的datas里去
print(datas)

Todata = {}
for keys,values in field_name.items():#在sqlsever数据库表-字段列表里面在每张表的名字和字段元组
    if keys not in Todata.keys():
        Todata[keys] = []
        for k,v in datas.items():#对datas里面的数据进行循环
            if k == keys:#表对应
                for data in v:#循环读取每条数据
                    list = {}
                    for k1,v1 in data.items():#拆分健值对，找到每条数据的值
                        if k1 in values:##判断是否是需要的字段
                            list[k1] = v1
                    #print(list)
                    Todata[keys].append(list)
print(Todata)

sql_into = ''
for key,value in Todata.items():
    for j in value:
        list1 = []
        for k,v in j.items():
            list1.append(k)
            list2.append(v)
        field = tuple(list1)
        values = tuple(list2)
        sql_into += 'insert into {}{} values {};'.format(key,str(field).replace("'", ''),values)
print(sql_into)
cursor.execute(sql_into)
conn.commit()
